using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using DTO;

namespace DAO
{
    public class PhieuThueDAO:DataProvider
    {
        public DataTable TimPhieuThue(PhieuThueDTO ptdto)
        {
            string sql = "select * from PhieuThue where 1=1 ";
            if (ptdto.MaNV != 0)
                sql += " and MaNV=" + ptdto.MaNV.ToString();
            if (ptdto.MaPhong != 0)
                sql += " and MaPhong=" + ptdto.MaPhong.ToString();
            sql += " and Xoa=" + ptdto.Xoa.ToString();
            DataTable dt = DocBang(sql);
            return dt;
        }
        public DataTable TimPhieuThueTheoTenPhong(string tenphong)
        {
            string sql = "select  pt.*, kh.*, p.Ten, lp.*,lkh.Ten from PhieuThue pt, Phong p, ChiTietPT ct, KhachHang kh, LoaiKH lkh, LoaiPhong lp ";
            sql += " where pt.ThanhToan=FALSE and kh.MaLoai=lkh.Ma and p.MaLoai=lp.Ma and ct.MaPT=pt.Ma and ct.MaKH=kh.Ma and pt.MaPhong=p.Ma and p.Ten='" + tenphong + "' and p.TinhTrang=FALSE Order by pt.Ma Desc";
            
            return DocBang(sql);
        }
        public void Them(PhieuThueDTO ptdto)
        {
            OleDbConnection cn = new OleDbConnection();
            cn = this.KetNoi();
            string str = "Insert into PhieuThue(NgayThue, MaNV, MaPhong) values (?, ?, ?)";

            OleDbCommand cmd = new OleDbCommand(str, cn);

            cmd.Parameters.Add("@NgayThue", OleDbType.Date);
            cmd.Parameters.Add("@MaNV", OleDbType.Integer);
            cmd.Parameters.Add("@MaPhong", OleDbType.Integer);

            cmd.Parameters["@NgayThue"].Value = ptdto.NgayThue;
            cmd.Parameters["@MaNV"].Value = ptdto.MaNV;
            cmd.Parameters["@MaPhong"].Value = ptdto.MaPhong;

            cmd.ExecuteNonQuery();

            cn.Close();
        }
        public void Sua(PhieuThueDTO ptdto)
        {
            OleDbConnection cn = KetNoi();
            string sql = "Update PhieuThue Set MaPhong = ?  Where Ma = ?";
            OleDbCommand cmd = new OleDbCommand(sql, cn);
            cmd.Parameters.Add("@MaPhong", OleDbType.Integer);
            cmd.Parameters.Add("@Ma", OleDbType.Integer);

            cmd.Parameters["@MaPhong"].Value = ptdto.MaPhong;
            cmd.Parameters["@Ma"].Value = ptdto.Ma;

            cmd.ExecuteNonQuery();
            cn.Close();
        }
        public void Xoa(int mapt)
        {
            OleDbConnection cn = KetNoi();
            string sql = "Delete From PhieuThue Where Ma = ? ";
            OleDbCommand cmd = new OleDbCommand(sql, cn);

            cmd.Parameters.Add("@Ma", OleDbType.Integer);

            cmd.Parameters["@Ma"].Value = mapt;

            cmd.ExecuteNonQuery();
            cn.Close();
        }
        public void XoaChiTietPhieuThue(int mapt)
        {
            OleDbConnection cn = KetNoi();
            string sql="Delete From ChiTietPT Where MaPT = ? ";
            OleDbCommand cmd=new OleDbCommand(sql,cn);

            cmd.Parameters.Add("@MaPT", OleDbType.Integer);

            cmd.Parameters["@MaPT"].Value = mapt;

            cmd.ExecuteNonQuery();
            cn.Close();
        }
        public DataTable LayMaxMa()
        {
            string sql="select Max(Ma) from PhieuThue";
            return DocBang(sql);
        }
        public void ThemChiTiet(DataTable dt,int mapt)
        {
            string sql = "select * from ChiTietPT where MaPT="+mapt.ToString();
            UpdateBang(sql, dt);
        }
        public DataTable LayBangChiTietPT(int mapt)
        {
            string sql = "select * from ChiTietPT where MaPT="+mapt.ToString();
            return DocCauTruc(sql);
        }
        public DataTable LayThongTinPhong(string tenphong)
        {
            string sql="SELECT p.*, pt.*, ct.*, lp.DonGia, lkh.* ";
                sql += " FROM Phong p, PhieuThue pt, ChiTietPT ct, KhachHang kh, LoaiPhong lp, LoaiKH lkh ";
                sql += " WHERE lkh.Ma=kh.MaLoai And ct.MaKH=kh.Ma And p.MaLoai=lp.Ma And pt.MaPhong=p.Ma And ct.MaPT=pt.Ma And pt.ThanhToan=False And p.Ten='"+tenphong+"'";

            return DocBang(sql);
        }
    }
}
